import os
import psycopg2
import psycopg2.extras
import sys
from shapely.wkt import dumps, loads
import constant
import datetime
import glob


aqstime_query = "select aqstime from res.satresampmod04 where filename like '%{0}%'"

#insert_query="INSERT INTO {0}(aqstime, rasref, filename, filepath, gridid,pmid) VALUES ('{1}'::timestamp, '{2}'::raster, '{3}', '{4}', 1, 1)" 
insert_query="INSERT INTO {0}(aqstime, filename, filepath, gridid,pmid) VALUES ('{1}'::timestamp, '{2}', '{3}', 1, 1)" 


def insertMOD04():
	aod_command = "raster2pgsql -a -f rasref -F {0} prodpm.prodmodispm_vn_collection0"
	folder = "/var/www/html/fimo/apom/Product/ProdMODPM/2013/*/*uk.tif"
	for full_file in glob.iglob(folder):
		
		start_index = full_file.rfind("/") + 1
		end_index = full_file.rfind(".hdf")
		file_name = full_file[start_index:end_index]
		
		start_path = full_file.rfind("/fimo/apom") + 1
		file_path = full_file[start_path:start_index]

		con = None
		try:
			con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
			cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
			cur.execute(aqstime_query.format(file_name))
			row = cur.fetchone()

			aqstime=row["aqstime"]

			# resampled file path
			aod_file = full_file
			if os.path.isfile(aod_file):
				cmd = aod_command.format(aod_file)
				aod_script = os.popen(cmd).read()
				first_index = aod_script.find("('")
				last_index = aod_script.find("':")
				aod_ref = aod_script[first_index+2:last_index]
				
				
				aod_insert = insert_query.format("prodpm.prodmodispm_vn_collection0",aqstime,aod_ref,file_name+".hdf_DT_10km_uk.tif",file_path)
			
			
				cur.execute(aod_insert)
				cur.execute('COMMIT')
				print(file_name)
				
		

		except psycopg2.DatabaseError, e:
			print 'Error %s' % e    
			sys.exit(1)
		finally:
			if con:
				con.close()
			

	
if __name__ == '__main__':
	insertMOD04()
	print "MOD04 done"
	#insertMOD04(mod04_query.format("apom.satresampmyd04"))
	#print "MYD04 done"
	#insertMYD07(myd07_query)
	#print "MYD07 done"
	#insertMOD07(mod04_query.format("apom.satresampmod07temperature"))
	#print "MOD07 done"
	#insertMYD07(mod04_query.format("apom.satresampmyd07temperature"))
	#print "MOD07 done"
	#insertAll04(mod04_query.format("apom.satresampall04"))
	#print "All04 done"

	
